﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Web.Caching;
using System.Web.UI;
using System.Web.UI.WebControls;
using PetShop.Web.Entities;
using PetShop.Web.WebComponents;

namespace PetShop.Web
{
    public partial class WebFormSearch : Page
    {
        protected void PageChanged(object sender, DataGridPageChangedEventArgs e)
        {
            products.CurrentPageIndex = e.NewPageIndex;


            string searchKey = CleanString.InputText(Request["keywords"], 100);

            if (searchKey != "")
            {
                string cacheKey = "search" + searchKey;


                if (Cache[cacheKey] != null)
                {
                    products.DataSource = Cache[cacheKey];
                }
                else
                {
                    string[] keywords = searchKey.Split();

                    var productsBySearch = new List<ProductInfo>();

                    using (var cmd = new SqlCommand())
                    {
                        using (var conn = new SqlConnection(ConfigurationManager.AppSettings["SQLConnString"]))
                        {
                            int numKeywords = keywords.Length;
                            var sql = new StringBuilder("SELECT ProductId, Name, Descn FROM Product WHERE ((");

                            cmd.Connection = conn;

                            cmd.CommandType = CommandType.Text;
                            for (int i = 0; i < numKeywords; i++)
                            {
                                sql.Append(
                                    string.Format(
                                        "LOWER(Name) LIKE '%' + {0} + '%' OR LOWER(Category) LIKE '%' + {0} + '%'",
                                        "@Keyword" + i));
                                sql.Append(i + 1 < numKeywords ? ") OR (" : "))");

                                var parm = new SqlParameter("@Keyword" + i, SqlDbType.VarChar, 80);
                                parm.Value = keywords[i];
                                cmd.Parameters.Add(parm);
                            }

                            cmd.CommandText = sql.ToString();
                            conn.Open();
                            using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                            {
                                while (rdr.Read())
                                {
                                    var product = new ProductInfo(rdr.GetString(0), rdr.GetString(1), rdr.GetString(2));
                                    productsBySearch.Add(product);
                                }
                            }
                        }
                    }


                    Cache.Add(cacheKey, productsBySearch, null, DateTime.Now.AddHours(12), Cache.NoSlidingExpiration,
                              CacheItemPriority.High, null);
                    products.DataSource = productsBySearch;
                }


                products.DataBind();
            }
        }
    }
}